package com.carlinfo.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

/**
 * 封装jdbc
 * @author LENOVO-CRG
 *
 */
public class DbUtil
{
	/**
	 * 在自己的类中只new一次
	 */
	private static final DbUtil DBUTIL = new DbUtil();
	
	/**
	 * 驱动
	 */
	private String driver = ConstantFinalUtil.BUNDLE.getString("jdbc.driver");
	
	/**
	 * 获取连接
	 */
	private String url = ConstantFinalUtil.BUNDLE.getString("jdbc.url");
	
	/**
	 * 用户名
	 */
	private String user = ConstantFinalUtil.BUNDLE.getString("jdbc.user");
	
	/**
	 * 密码
	 */
	private String password = ConstantFinalUtil.BUNDLE.getString("jdbc.password");
	
	/**
	 * 加载驱动,默认构造方法设置为private
	 */
	private DbUtil()
	{
		try
		{
			Class.forName(driver);
		} catch (ClassNotFoundException e)
		{
			System.out.println("加载驱动失败了");
			e.printStackTrace();
		}
	}
	
	/**
	 * 单例模式
	 * @return
	 */
	public static DbUtil getInstance()
	{
		return DBUTIL;
	}
	
	/**
	 * 获取连接
	 * @return
	 */
	public Connection getConnection()
	{
		Connection conn = null;
		try
		{
			conn = DriverManager.getConnection(url, user, password);
			return conn;
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 关闭链接
	 * @param rs
	 * @param st
	 * @param conn
	 */
	public void closeAll(ResultSet rs,Statement st,Connection conn)
	{
		try
		{
			if(rs!=null) 
			{
				rs.close();
				rs = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		try
		{
			if(st!=null)
			{
				st.close();
				st = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		try
		{
			if(conn!=null)
			{
				conn.close();
				conn = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args)
	{
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		DbUtil dbUtil = DbUtil.getInstance();
		try
		{
			String sql = "select * from a_dynasty";
			conn = dbUtil.getConnection();
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			System.out.println("id\t名字\t首都\t国祚\t状态\t开国时间\t\t\t创建时间\t\t\t更新时间\t\t\t发布时间");
			while(rs.next())
			{
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String capital = rs.getString("capital");
				short age = rs.getShort("age");
				int status = rs.getInt("status");
				Date st = rs.getTimestamp("st");
				Date createTime = rs.getTimestamp("createTime");
				Date updateTime = rs.getTimestamp("updateTime");
				Date pubTime = rs.getTimestamp("pubTime");
				System.out.println(id+"\t"+name+"\t"+capital+"\t"+age+"\t"+status
						+"\t"+st+"\t"+createTime+"\t"+updateTime+"\t"+pubTime);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			dbUtil.closeAll(rs,pst, conn);
		}
	}
}